/*
 * Created on 20.11.2003
 * Local Projects Database
 * Mihai Postelnicu (mihai@ro-gateway.org)
 * (comparator) 2003 by eRomania Gateway
 */
package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Comparator;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.OrgType;
import ro.gateway.aida.utils.LocalisedSortableList;

/**
 * @author Mihai Postelnicu
 * 
 * e-mail (mihai@ro-gateway.org) (c) 2003 by eRomania Gateway
 */
public class OrgTypeDB extends DBPersistenceManager {

	private OrgTypeDB(PersistenceToken token) {
		super(token);
	}

	public static OrgTypeDB getManager(PersistenceToken token) {
		return new OrgTypeDB(token);
	}

	public void insert(int id, String lang, String text) throws SQLException {
		Connection con = getConnection();
		try {
			PreparedStatement ps;
			if (id == 0) {
				ps = con.prepareStatement(ADD_FIRST_LANG);
				ps.setString(1, text);
				ps.setString(2, lang);

			} else {
				if (isLang(id, lang))
					ps = con.prepareStatement(UPDATE_LANG);
				else
					ps = con.prepareStatement(ADD_LANG);
				ps.setString(1, text);
				ps.setInt(2, id);
				ps.setString(3, lang);

			}
			ps.executeUpdate();
			ps.close();

		} finally {
			con.close();
		}
	}


	public boolean delete(int id)  throws SQLException{
		if (id == 0)
			return false;
		Connection con = getConnection();
		try {	
			PreparedStatement ps = con.prepareStatement(DELETE);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();			
		}
		catch (SQLException e) {	
			return false;
		}
		finally {
			con.close();
		}
		return true;
	}

	
	
	public boolean isLang(int id, String lang) throws SQLException {
		if (id == 0)
			return false;
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(IS_LANG);
			ps.setInt(1, id);
			ps.setString(2, lang);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				int c = rs.getInt(1);
				if (c == 0)
					return false;
				return true;
			}
			rs.close();
			ps.close();

		} finally {
			con.close();
		}
		return false;
	}

	public OrgType getById(int id) throws SQLException {
		Connection con = getConnection();
		OrgType ot = new OrgType();
		try {
			PreparedStatement ps = con.prepareStatement(GET_TYPE);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				ot.setId(rs.getInt(1));
				ot.getName().setString(rs.getString(2), rs.getString(3));
			}
			rs.close();
			ps.close();

		} finally {
			con.close();
		}

		return ot;
	}

	public LocalisedSortableList getAllLocalized(String lang)
			throws SQLException {
		Comparator c = new OrgType.LangComparator(lang);
		LocalisedSortableList v = new LocalisedSortableList(lang, c);
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(TYPES_LIST);

			ResultSet rs = ps.executeQuery();

			int lastId = -1;
			OrgType ot = null;
			while (rs.next()) {
				if (lastId != rs.getInt(1)) {
					ot = new OrgType();
					v.add(ot);					
				}
				lastId = rs.getInt(1);

				ot.setId(rs.getInt(1));
				ot.getName().setString(rs.getString(2), rs.getString(3));
			}

			rs.close();
			ps.close();

		} finally {
			con.close();
		}

		return v;
	}

	private static final String DELETE = "DELETE FROM org_cats WHERE id=?";
	
	private static final String IS_LANG = "SELECT count(*) FROM org_cats WHERE id=? AND lang=?";

	private static final String ADD_FIRST_LANG = "INSERT INTO org_cats (name,lang) VALUES (?,?)";

	private static final String ADD_LANG = "INSERT INTO org_cats (name,id,lang) VALUES (?,?,?)";

	private static final String UPDATE_LANG = "UPDATE org_cats SET name=? WHERE id=? AND lang=?";

	private static final String GET_TYPE = "SELECT id,lang,name FROM org_cats WHERE id=?";

	private static final String TYPES_LIST = "SELECT id,lang,name FROM org_cats ORDER BY id";
}